﻿using System;
using System.Collections.Generic;
using SimpleJSON;
using System.IO;
using System.Data.OleDb;
using System.Data;

namespace ConfigTool
{
    public class ExcelToJson
    {
        private string sourcePath;
        private string savePath;
        private Dictionary<string, JSONClass> jsonDic = new Dictionary<string, JSONClass>();
        private int fileNum;
        public Dictionary<string, JSONClass> JsonDic
        {
            get
            {
                return jsonDic;
            }
        }

        public ExcelToJson(string _sourcePath, string _savePath)
        {
            sourcePath = _sourcePath;
            savePath = _savePath;

            Tool.CreateDirectory(sourcePath);
            Tool.CreateDirectory(savePath);
        }

        public void CreateJsonFiles()
        {
            Tool.ClearFiles(savePath);
            AllConvertToJsons();
            foreach (var a in jsonDic)
            {
                string path = string.Format("{0}\\{1}.json", savePath, a.Key);
                Tool.CreateFile(path, a.Value.ToString());
            }
        }

        public int GetFileCount()
        {
            return fileNum;
        }

        private void AllConvertToJsons()
        {
            jsonDic.Clear();
            var excelPathList = GetAllExcel();
            fileNum = excelPathList.Count;
            foreach (var a in excelPathList)
            {
                ConvertToJson(a);
            }
        }

        private void ConvertToJson(string _excelPath)
        {
            OleDbConnection connection = CreateExcelOleDbConnection(_excelPath);
            if (connection == null)
            {
                Console.WriteLine("无法成功生成OleDbConnection");
                return;
            }
            List<OleDbDataReader> readers = InitExcel(_excelPath, connection);
            ReadersToJson(readers, connection);
        }

        #region ConvertToJson 具体实现方法
        private List<OleDbDataReader> InitExcel(string _excelPath, OleDbConnection _connection)
        {
            if (_connection == null)
            {
                return null;
            }
            _connection.Open();
            DataTable dataTable = _connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            List<OleDbDataReader> readers = new List<OleDbDataReader>();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                string sheetName = dataTable.Rows[i]["Table_Name"].ToString();
                if (sheetName.Contains("#"))//表中页签含有'#'为注释
                {
                    continue;
                }
                OleDbCommand command = new OleDbCommand(string.Format("select * from [{0}]", sheetName), _connection);
                readers.Add(command.ExecuteReader());
            }
            return readers;
        }

        private OleDbConnection CreateExcelOleDbConnection(string _excelPath)
        {
            if (!File.Exists(_excelPath))
            {
                Console.WriteLine("未找到指定文件" + _excelPath);
                return null;
            }

            string strExtension = Path.GetExtension(_excelPath);
            string initStr = string.Empty;
            switch (strExtension)
            {
                case ".xls":
                    initStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"", _excelPath);
                    break;
                case ".xlsx":
                    initStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"", _excelPath);
                    break;
                default:
                    Console.WriteLine("目标文件不是excel文件");
                    return null;
                    break;
            }

            return new OleDbConnection(initStr);
        }

        private void ReadersToJson(List<OleDbDataReader> _readers, OleDbConnection _connection)
        {
            for (int i = 0; i < _readers.Count; i++)
            {
                OleDbDataReader reader = _readers[i];

                //获取表头
                reader.Read();
                string configTitle = reader[0].ToString();
                if (configTitle == null || configTitle == "")
                {
                    Console.WriteLine("表头填写不正确");
                }
                int nextLineIndex = 1;

                //跳过无用行
                while (reader.Read())
                {
                    nextLineIndex++;
                    if (reader[0].ToString() == "Start")
                    {
                        break;
                    }
                }

                //存储json的key，simplejson无法直接获取key
                int maxRowNum = reader.FieldCount;
                JSONClass jsonKey = new JSONClass();
                JSONClass jsonData = new JSONClass();
                reader.Read();
                for (int j = 1; j < maxRowNum; j++)
                {
                    if (reader[j].ToString() == "")//辅助列与正常数据之间要有空行
                    {
                        maxRowNum = j;
                        break;
                    }
                    string key = reader[j].ToString();
                    jsonKey.Add(key, key);
                }
                jsonData.Add("variate", jsonKey);

                //依次按行读取有效数据
                while (reader.Read())
                {
                    string key = reader[0].ToString();
                    if (key == "End")
                    {
                        break;
                    }

                    JSONClass curLineJson = new JSONClass();
                    for (int j = 1; j < maxRowNum; j++)
                    {
                        curLineJson.Add(jsonKey[j - 1], reader[j].ToString());
                    }
                    jsonData.Add(key, curLineJson);
                }
                reader.Close();
                
                //将当前页签的json文件存储到字典中
                if (jsonDic.ContainsKey(configTitle))
                {
                    jsonDic[configTitle].Add(jsonData);
                }
                else
                {
                    jsonDic.Add(configTitle, jsonData);
                }
            }
            _connection.Close();
        }
        #endregion ConvertToJson 具体实现方法

        /// <summary>
        /// 获取源文件夹中的excel文件，文件名中的"###"作为注释，含有"###"的文件不计进行转换
        /// </summary>
        /// <returns>excel文件路径列表</returns>
        private List<string> GetAllExcel()
        {
            DirectoryInfo dirInfo = new DirectoryInfo(sourcePath);
            FileInfo[] fileInfos = dirInfo.GetFiles();
            List<string> rtnValue = new List<string>();
            foreach (var a in fileInfos)
            {
                if (a.FullName.Contains("###"))
                {
                    continue;
                }
                if (a.FullName.Contains(".xlsx")
                    || a.FullName.Contains(".xls"))
                {
                    if (rtnValue.Contains(a.FullName))
                    {
                        continue;
                    }
                    rtnValue.Add(a.FullName);
                }
            }
            return rtnValue;
        }
    }
}
